package database.dbObjects;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

import main.Main;
import objects.Task;


public interface IDBTask extends IDBObject {
	
	public class DBTask extends DBObject {
		
		/*
		 * Methods are final for optimization of the code (by the compiler)
		 * It is not possible anymore to inherit them further 
		 */
		
		public static final int maxLengthName = 50;
		
		
		public void init() throws Exception {
			
			createTable();
			createTriggers();
		}
		
		private void createTable() throws Exception {
			
			String query = "create table Task( " +
						   "Name varchar(" + maxLengthName + ") unique not null, " +
						   "Description varchar(" + maxLengthDesc + "), " +
						   "Done boolean not null, " +
						   "Grades integer not null, " +
						   "MoreInformation varchar(" + maxLengthInfo + "), " +
						   "primary key (Name) " +
						       "on conflict abort, " +
						   "check (Grades between 0 and 20));";
	        
			executeCreation(query);
		}
		
		private final void createTriggers() throws Exception {
			
			String query;
			query = "create trigger update_task_name after update of Name on Task " +
			   	    "begin " +
			   	    	"update Exam set Name = new.Name " +
			   	    		"where (Name = old.Name); " +
				    	"update Project set Name = new.Name " +
				    		"where (Name = old.Name); " +
				    	"update RelCourseTask set Task_Name = new.Name " +
				    		"where (Task_Name = old.Name); " +
				    	"update RelTaskEvent set Task_Name = new.Name " +
				    		"where (Task_Name = old.Name); " +
				    	"update RelTaskFile set Task_Name = new.Name " +
				    		"where (Task_Name = old.Name); " +
				    "end;";
			executeCreation(query);
			
			query = "create trigger delete_task_name before delete on Task " +
					"begin " +
						"delete from Exam where (Name = old.Name); " +
						"delete from Project where (Name = old.Name); " +
	    	   			"delete from RelCourseTask where (Task_Name = old.Name); " +
	    	   			"delete from RelTaskEvent where (Task_Name = old.Name); " +
	    	   			"delete from RelTaskFile where (Task_Name = old.Name); " +
	    	   		"end;";
			executeCreation(query);
		}
		
		public final boolean add(String name, String desc, Boolean done, 
				Integer grades, String moreInfo) throws Exception {
			
			checkConsistance(name, desc, done, grades, moreInfo);
			
			Vector<Object> v = new Vector<Object>(5);
			v.add(0, name);
			v.add(1, desc);
			v.add(2, done);
			v.add(3, grades);
			v.add(4, moreInfo);
			
			return super.add("Task", v);
		}
		
		public final boolean delete(String name) throws Exception {
			
			checkName(name);
			
			return super.delete("Task", "Name", name);
		}
		
		public final boolean updateName(String name, String newName) throws Exception {
			
			checkName(name);
			checkName(newName);
			
			return super.update("Task", "Name", name, "Name", newName);
		}
		
		public final boolean updateDescription(String name, String newDesc) throws Exception {
			
			checkName(name);
			checkDescription(newDesc);
			
			return super.update("Task", "Name", name, "Description", newDesc);
		}
		
		public final boolean updateDone(String name, Boolean done) throws Exception {
			
			checkName(name);
			checkDone(done);
			
			return super.update("Task", "Name", name, "Done", done);
		}
		
		public final boolean updateGrades(String name, Integer newGrades) throws Exception {
			
			checkName(name);
			checkGrades(newGrades);
			
			return super.update("Task", "Name", name, "Grades", newGrades);
		}
		
		public final boolean updateMoreInformation(String name, String newInfo) throws Exception {
			
			checkName(name);
			checkMoreInformation(newInfo);
			
			return super.update("Task", "Name", name, "MoreInformation", newInfo);
		}
		
		public Task getTask(String name) throws Exception {
			// Returns the requested object or null if nothing was found
			
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from Task " +
											 "where (Name = '" + name + "');");
			if (rs.next()) {
				Task t = new Task(rs.getString("Name"), rs.getString("Description"),
						rs.getBoolean("Done"), rs.getInt("Grades"), 
						rs.getString("MoreInformation"));
				rs.close();
				return t;
			}
			rs.close();
			return null;
		}
		
		public Vector<Task> getAllTasks() throws Exception {
			
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from Task " + 
					"where (Name not in (select Name from Project) and " + 
					"Name not in (select Name from Exam));");
			
			Vector<Task> v = new Vector<Task>();
			int counter = 0;
			while (rs.next()) {
				v.add(counter, new objects.Task(rs.getString("Name"), 
						rs.getString("Description"), rs.getBoolean("Done"), 
						rs.getInt("Grades"), rs.getString("MoreInformation")));
				counter++;
			}
			rs.close();
			return v;
		}
		
		protected final void checkConsistance(String name, String desc, Boolean done, 
				Integer grades, String moreInfo) throws Exception {
			
			checkName(name);
			checkDescription(desc);
			checkDone(done);
			checkGrades(grades);
			checkMoreInformation(moreInfo);
		}
		
		public final void checkName(String name) throws Exception {
			
			if (name == null) { throw (new Exception("Name needed!")); }
			if (name.length() < 1) {
				throw (new Exception("Name has to be at least 1 character long!"));
			}
			if (name.length() > maxLengthName) {
				throw (new Exception("Name exceeds maximum length (" +
						maxLengthName + " characters)!"));
			}
		}
		
		private final void checkDescription(String desc) throws Exception {
			
			if (desc == null) { desc = "Nothing."; }
			if (desc.length() < 1) {
				throw (new Exception("Description has to be at least 1 character long!"));
			}
			if (desc.length() > maxLengthDesc) {
				throw (new Exception("Description exceeds maximum length (" +
						maxLengthDesc + " characters)!"));
			}
		}
		
		private final void checkDone(Boolean done) throws Exception {
			
			if (done == null) { throw (new Exception("Done needed!")); }
		}
		
		private final void checkGrades(Integer grades) throws Exception {
			
			if (grades == null) { throw (new Exception("Grades needed!")); }
			if (grades < 0) { throw (new Exception("Grades can't be negative!")); }
			if (grades > 20) { throw (new Exception("Grades can't be more than 20!")); }
		}
		
		private final void checkMoreInformation(String moreInfo) throws Exception {
			
			if (moreInfo == null) { moreInfo = "Nothing."; }
			if (moreInfo.length() > maxLengthInfo) {
				throw (new Exception("MoreInformation exceeds maximum length (" +
						maxLengthInfo + " characters)!"));
			}
		}
		
		
		private final int maxLengthDesc = 200;
		private final int maxLengthInfo = 200;
		
	}
	
}
